
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimMerchant_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimMerchant_Update]
GO


create procedure dbo.p_DimMerchant_Update (
	@MerchantKey int,
	@MerchantIdentifier varchar (30),
	@MerchantFullName nvarchar (150),
	@Status nvarchar (50),
	@MerchantCategory nvarchar (50),
	@MerchantGroup nvarchar (50),
	@ParentHeadOfficeKey int,
	@StreetNumber int,
	@StreetNumberSuffix varchar (50),
	@StreetName nvarchar (100),
	@StreetType nvarchar (100),
	@streetDirection nvarchar (100),
	@AddressType nvarchar (100),
	@AddressTypeIdentifier nvarchar (100),
	@MinorMunicipality nvarchar (100),
	@MajorMunicipality nvarchar (100),
	@GoverningDistrict nvarchar (100),
	@PostalCode nvarchar (20),
	@Country nvarchar (100),
	@GeoLatitude varchar (20),
	@GeoLongitude varchar (20),
	@GeocodingSpatial geography)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimMerchantUpdate
        else
	    save transaction txnDimMerchantUpdate

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	if (not exists (select 1 
		          from dbo.DimMerchant with (NOLOCK)
			  where (MerchantKey = @ParentHeadOfficeKey)))
	begin

		set @Error = -3
		goto cleanup

	end			

	update dbo.DimMerchant set MerchantIdentifier = @MerchantIdentifier,
				   MerchantFullName = @MerchantFullName,
				   Status = @Status,
				   MerchantCategory = @MerchantCategory,
				   MerchantGroup = @MerchantGroup,
				   ParentHeadOfficeKey = @ParentHeadOfficeKey,
				   StreetNumber = @StreetNumber,
				   StreetNumberSuffix = @StreetNumberSuffix,
				   StreetName = @StreetName,
				   StreetType = @StreetType,
				   StreetDirection = @StreetDirection,
				   AddressType = @AddressType,
				   AddressTypeIdentifier = @AddressTypeIdentifier,
				   MinorMunicipality = @MinorMunicipality,
				   MajorMunicipality = @MajorMunicipality,
				   GoverningDistrict = @GoverningDistrict,
				   PostalCode = @PostalCode,
				   Country = @Country,
				   GeoLatitude = @GeoLatitude,
				   GeoLongitude = @GeoLongitude,
				   GeocodingSpatial = @GeocodngSpatial
	  where (MerchantKey = @MerchantKey)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -2
	if (@Error <> 0) goto cleanup

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimMerchantUpdate
		else
		if (@inTranCount = 0) commit transaction txnDimMerchantUpdate

	end

	return @Error

end
GO

grant execute on dbo.p_DimMerchant_Update  to [db_datawriter]
go
